﻿
CREATE PROCEDURE [dbo].[proc_Project_FinanceAudit]
	(
		@Id int,
		@UserName nvarchar(50)
	)
AS
BEGIN
BEGIN TRANSACTION--开始事务
DECLARE @errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0

--By 赵洪涛 2012-08-13 将16:30后的数据调整到第2天 Begin--
	Declare @nDays int,@nHour int,@nMinute int,@dToday DateTime
--	Set @dToday = GetDate() --初始化日期
	--16:30分调整日期
--	Set @nHour = 16
--	Set @nMinute = 30

	--今天+1天与下月1日进行比较，相同则说明今天是本月最后1天
--	If Month(GetDate())<12
--		Set @nDays=DateDiff(d,DateAdd(d,1,GetDate()),Convert(datetime,Convert(varchar(4),Year(GetDate())) + '-' + Convert(varchar(2),Month(GetDate())+1) + '-1'))
--	Else
--		Set @nDays=DateDiff(d,DateAdd(d,1,GetDate()),Convert(datetime,Convert(varchar(4),Year(GetDate())+1) + '-01-01'))
--	If @nDays>0 --不是最后1天
--	Begin
--		If DatePart(hh,GetDate())>@nHour Or (DatePart(hh,GetDate())=@nHour And DatePart(mi,GetDate())>@nMinute)	--超过16:30分
--			Set @dToday = DateAdd(hh,12,GetDate())	--加12小时
--	End
Set @dToday=dbo.Get_SetDateTime()
--By 赵洪涛 2012-08-13 将16:30后的数据调整到第2天 End --

--By 赵洪涛 2012-03-14 开始
	Declare @project_Je decimal(18, 2),	@CompanyId_1 int,@CompanyId_2 int, @ProductId int,@FinanceAudit int
	Declare @nAccId_1 int,@nAccId_2 int,@dRq datetime,@nId int,@nYe decimal(18, 2),@OId varchar(50),@shl int
	
	--取(预存账号所属的)公司编号、产品编号、支出金额
	Select @CompanyId_2=CompanyId,@ProductId=ProductId,@project_Je=CostPrice,@FinanceAudit=FinanceAudit,@OId=OrderId From Project Where Id=@Id

	If @FinanceAudit<>1		--已经审核过（重复提交）
	Begin
		RollBack Tran	--取消事务
		Return 0
	End

	--取预存账号和总代理账号	
	Select @nAccId_2=id,@nAccId_1=syzh From AB_Interface Where CompanyId=@CompanyId_2 And syzh>0 And ProductId Like '%{'+Convert(varchar(10),@ProductId)+'}%'

	If @nAccId_2 Is Not Null	--找到该账号(找不到则说明该产品不需提供商或提供商就是龙采 无支出成本)
	Begin		
		--处理预存账号 开始
		Select Top 1 @nId=id,@nYe=ye,@dRq=UpTime From Ab_List Where Iid=@nAccId_2 And AB_Type=0 Order By UpTime Desc	--该预存账号的最后一笔数据
		If @nId Is Null		--该模块第一次使用/或是新增产品了
		Begin
			Insert Into AB_List(CompanyId,Iid,je,ye,UpTime,Items,syzh) values(@CompanyId_2,@nAccId_2,@project_Je,0-@project_Je,@dToday,1,@nAccId_1)		--为该预存账号添加1笔当日支出
		End
		Else	--正常时
		Begin
			Set @nDays=DateDiff(dd,@dRq,@dToday)
			If @nDays=0 --今天该预存账号已经有数据了————修改该预存账号当日的余额和支出金额
				Update AB_List Set je=je+@project_Je,ye=ye-@project_Je,Items=Items+1 Where id=@nId
			Else	--今天该预存账号尚未产生数据————插入该预存账号最近的哪一笔余额和支出金额
				Insert Into AB_List(CompanyId,Iid,je,ye,UpTime,Items,syzh) values(@CompanyId_2,@nAccId_2,@project_Je,@nYe-@project_Je,@dToday,1,@nAccId_1)
		End
	--处理预存账号 结束

	--处理总代账号 开始
		--取总代理的公司编号
		Select @CompanyId_1=CompanyId From AB_Interface Where id=@nAccId_1 And syzh=0
		Set @nId = Null --By ZHT 2012-08-18 ******
		Select Top 1 @nId=id,@nYe=ye,@dRq=UpTime From Ab_List Where Iid=@nAccId_1 And AB_Type=0 Order By UpTime Desc	--总代账号的最后一笔数据
		If @nId Is Null		--该模块第一次使用/或是新增产品了
		Begin
			Insert Into AB_List(CompanyId,Iid,je,ye,UpTime,Items,rootId,syzh) values(@CompanyId_1,@nAccId_1,@project_Je,0-@project_Je,@dToday,1,1,0)		--为总代账号添加1笔当日支出
		End
		Else	--正常时
		Begin
			Set @nDays=DateDiff(dd,@dRq,@dToday)
			If @nDays=0 --今天总代账号已经有数据了————修改总代账号当日的余额和支出金额
				Update AB_List Set je=je+@project_Je,ye=ye-@project_Je,Items=Items+1 Where id=@nId
			Else	--今天总代账号尚未产生数据————插入总代账号最近的哪一笔余额和支出金额(计算后的)
				Insert Into AB_List(CompanyId,Iid,je,ye,UpTime,Items,rootId,syzh) values(@CompanyId_1,@nAccId_1,@project_Je,@nYe-@project_Je,@dToday,1,1,0)
		End
	--处理总代账号 结束
	End
--By 赵洪涛 2012-03-14 结束

	Update Project Set FinanceAudit=2,FinanceAuditDate=@dToday Where Id=@Id
	
		if @@ROWCOUNT<1
		begin
			set @errorSun=@errorSun+1		
			goto e;
		end
	
	Declare @PaymentTypeId int, @PaymentTypeName nvarchar(50), @BankId int, 
	@BankName nvarchar(50)
	
	Insert Into Finance_Outlay
	(
		CreateDate
		,TradeDate
		,Summary
		,CateOneId
		,CateOneName
		,CateTwoId
		,CateTwoName
		,SubjectId
		,SubjectName
		,PaymentTypeId
		,PaymentTypeName
		,BankId
		,BankName
		,OutlayReason
		,OutlayPrice
		,Agent
		,AgentName
		,UserName
		,EName
		,Description
		,BillNumber
		,CompanyIdCur
		,DepartmentIdCur
		,CompanyId,[cb_flag] --是否参与支出计算的标识，支出审核产生的支出不参与计算 2014-12-30
	)
	Select
		@dToday
		,Convert(nchar(10),@dToday,120)
		,SupplierName
		,SCateOneId
		,(Select Title From Finance_Subject_Parent Where Id=SCateOneId)
		,SCateTwoId
		,(Select Title From Finance_Subject_Parent Where Id=SCateTwoId)
		,SubjectId
		,(Select Title From Finance_Subject Where Id=Project.SubjectId)
		,(Select PaymentTypeId From [Order] Where OId=Project.OrderId)
		,(Select PaymentType From [Order] Where OId=Project.OrderId)
		,(Select BankId From [Order] Where OId=Project.OrderId)
		,(Select Bank From [Order] Where OId=Project.OrderId)
		,CusName +'---'+TypeName+'---'+ProductName
		,CostPrice
		,execperson
		,(SELECT e.EName FROM Employee e WHERE e.UserName=execperson)
		,@UserName
		,(Select EName From Employee Where UserName=@UserName)
		,Convert(VARCHAR(2000),DESCRIPTION)+ '('+CONVERT(VARCHAR,@id)+')'
		,'0000'
		,CompanyId
		,ExecDepartmentId
		,CompanyId,1
	From Project Where Id=@Id

			if @@ROWCOUNT<1
		begin
			set @errorSun=@errorSun+1
			goto e;
		END

--By 赵洪涛 2012-06-25 续费的单子且只有1笔百度预存款或服务费的，在此处自动完结
--2013-12-30调整为含百度预存款(1)、服务费(37)、优惠返款(23)中的任意一款产品的续费成单，
--只要该单的所有项目均已完成，该单就自动完结
If @ProductId=1 Or @ProductId=37 Or @ProductId=23
Begin
	Select @shl=Count(0) From Project Where OrderId=@OId And TypeId=2 And IsFinish<>1
	If @shl=0	--未完结的项目为0(都完结了)
		Update [Order] Set StateId=5,FinishDate=@dToday Where OId=@OId And TypeId=2 And StateId<5 And StateId>2 And (YingShouPrice-ShiShouPrice)<=0
End
--条件：1.续费成单(TypeId=2) 2.该单只含1个项目(@shl=1)
--3.百度续费或服务费(ProductId=@ProductId) 4.尾款为0((YingShouPrice-ShiShouPrice)<=0)
--5.成单状态在财务认款后、完结前(StateId<5 And StateId>2)
--By 赵洪涛 2012-06-25 结束
		
e:
    PRINT @errorSun
	if @errorSun>0
	begin
		rollback TRANSACTION
	
	end
	else
	begin
		commit TRANSACTION
	END	
End